package com.pb.metadata.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.pb.infra.oth.util.Constant;
import com.pb.infra.oth.util.JwtTokenConfig;
import com.pb.metadata.mapper.DataSourceMapper;
import com.pb.infra.oth.entity.common.Page;
import com.pb.infra.oth.entity.common.Result;
import com.pb.infra.oth.util.RedisUtil;
import com.pb.infra.oth.util.StringUtil;
import io.jsonwebtoken.Claims;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * 数据源管理
 * @author liaohaoyuan
 * @date 20210301
 */
@Service
public class DataSourceService {

    /**
     * 数据源表DAO映射
     */
    @Resource
    private DataSourceMapper dataSourceMapper;

    /**
     * redis数据访问
     */
    @Resource
    private RedisUtil redisUtil;

    /**
     * jwttoken配置类
     */
    @Resource
    private JwtTokenConfig jwtTokenConfig;

    /**
     * 查询分页数据
     * @param map 查询参数
     * @param page 页码
     * @return
     */
    public Result select(Map<String, Object> map, Page page){
        if (page != null && page.getPageIndex() != null && page.getPageSize() != null) {
            PageHelper.startPage(page.getPageIndex(), page.getPageSize());
        }
        List<Map<String, Object>> list = dataSourceMapper.select(map);
        return Result.ok(new PageInfo(list).getTotal(), list);
    }

    /**
     * 查询应用标识
     * @return
     */
    /*public List<Map<String,Object>> getAppIdList(Map<String,Object> param, HttpServletRequest request){
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        param.put("userId", userId);
        return dataSourceMapper.getAppIdList(param);
    }*/

    /**
     *获取数据源类型
     * @return
     */
    public List<Map<String,Object>> getDataSourceType(){
        return dataSourceMapper.getDataSourceType();
    }

    /**
     * 获取物理表类型
     * @return
     */
    public List<Map<String,Object>> getPhysicaTableTypeList(Map<String,Object> paramMap){
        return dataSourceMapper.getPhysicaTableTypeList(paramMap);
    }

    /**
     * 获取数据源用途
     * @return
     */
    public List<Map<String,Object>> getDatasourceUse(){
        return dataSourceMapper.getDatasourceUse();
    }

    /**
     * 获取数据库用户
     * @param paramMap
     * @return
     */
    public List<Map<String,Object>> getDatabaseUserList(Map<String,Object> paramMap){
        return dataSourceMapper.getDatabaseUserList(paramMap);
    }

    /**
     * 数据源主表保存
     * @return
     */
    @Transactional
    public Result insert(Map<String,Object> paramMap, HttpServletRequest request){
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        paramMap.put("createUser", userId);
        String databaseObjectName=(String)paramMap.get("databaseObjectName");
        paramMap.put("databaseObjectName",databaseObjectName.toUpperCase(Locale.ENGLISH));
        String datasourceId = dataSourceMapper.getDatasourceId(paramMap);
        if(datasourceId==null||"".equals(datasourceId)){
            paramMap.put("dataSourceId",StringUtil.randomHexString(32));
            dataSourceMapper.insert(paramMap);
        }else {
            String createUser=(String) paramMap.get("createUser") ;
            paramMap.put("dataSourceId",datasourceId);
            paramMap.put("updateUser",createUser);
            this.update(paramMap, request);
        }
        return Result.OK_EMPTY;
    }

    /**
     * 修改数据源
     * @param paramMap
     * @return
     */
    public Result update(Map<String,Object> paramMap, HttpServletRequest request){
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        paramMap.put("updateUser", userId);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        paramMap.put("updateTime",format.format(new Date()));
        if (!StringUtil.isEmpty(paramMap.get("checkFlag")) && "1".equals(paramMap.get("checkFlag").toString())) {
            String datasourceId = dataSourceMapper.getDatasourceId(paramMap);
            if (!StringUtil.isEmpty(datasourceId)) {
                return Result.error(Constant.EXIST,"该数据源已存在");
            }
        }
        dataSourceMapper.update(paramMap);
        return Result.OK_EMPTY;
    }

    /**
     * 删除数据源
     * @param list
     */
    public void deleteBatch(List<String> list){
        dataSourceMapper.deleteBatch(list);
    }

    /**
     * 启用OR停用
     * @param list
     */
    public void  startOrStop(List<Map<String,Object>> list){
        for (int i = 0; i <list.size() ; i++) {
            Map<String,Object> paramMap=list.get(i);
            String activeFlag=(String) paramMap.get("activeFlag");
            paramMap.put("activeFlag","1".equals(activeFlag)?"0":"1");
            dataSourceMapper.startOrStop(paramMap);
        }
    }

    /**
     * 锁定OR提交
     * @param paramMap
     */
    public void lockouts(Map<String,Object> paramMap){
        String obj = paramMap.get("dataSourceId").toString();
        obj=obj.substring(1,obj.length()-1);
        StringBuilder sbf = new StringBuilder();
       String[] strArr = obj.split(",");
        for (int i = 0; i <strArr.length ; i++) {
            String str = strArr[i].toString();
            sbf.append("\'"+str.trim()+"\'");
            if (i != strArr.length-1){
                sbf.append(",");
            }
        }
        paramMap.put("dataSourceId",sbf.toString());
       dataSourceMapper.lockouts(paramMap);
    }

    /**
     * 同步 把数据源添加的字段初始化
     * @param paramMap
     */
    @Transactional
    public void synchronismData(Map<String,Object> paramMap, HttpServletRequest request){
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        paramMap.put("UserId", userId);
        String databaseUserId = (String) paramMap.get("databaseUserId");
        String databaseUserName = dataSourceMapper.getDatabaseUserName(databaseUserId);
        paramMap.put("databaseUserName",databaseUserName);
        List<Map<String,Object>> list = dataSourceMapper.colList(paramMap);
        for (int i = 0; i < list.size(); i++) {
            String id = getUUID();
            list.get(i).put("columnId", id);
            dataSourceMapper.insertParamColumn(list.get(i));
        }
        dataSourceMapper.delParamDatasourceColumn(paramMap);
        dataSourceMapper.insertParamDatasourceColumn(paramMap);
    }

    public String getUUID() {
        String id = UUID.randomUUID().toString().replace("-", "");
        List<Map<String,Object>> list = dataSourceMapper.selUUID(id);
        if (list != null && list.size() > 0) {
            id = getUUID();
        }
        return id;
    }

    /**
     * 获取配置表中左表数据
     * @param paramMap
     * @return
     */
    public Result getLeftTableData(Map<String,Object> paramMap){
        paramMap.put("flag","1");
        List<Map<String, Object>> list = dataSourceMapper.getLeftTableData(paramMap);
        return Result.ok(list);
    }

    /**
     * 获取字段类型
     * @return
     */
    public List<Map<String,Object>> getFieldTypeDatas(){
        return dataSourceMapper.getFieldTypeDatas();
    }

    /**
     * 获取配置表右表数据
     * @param paramMap
     * @return
     */
    public List<Map<String,Object>> getRightTableData(Map<String,Object> paramMap){
        List<Map<String,Object>> list = dataSourceMapper.getRightTableData(paramMap);
        for(Map<String,Object> map : list){
            map.put("PK_FLAG","1".equals((String)map.get("PK_FLAG"))?true:false);
            map.put("NULL_FLAG","1".equals((String)map.get("NULL_FLAG"))?true:false);
            map.put("BK_FLAG","1".equals((String)map.get("BK_FLAG"))?true:false);
            map.put("REMARK_FLAG","1".equals((String)map.get("REMARK_FLAG"))?true:false);
            map.put("DIMENSION_FLAG","1".equals((String)map.get("DIMENSION_FLAG"))?true:false);
            map.put("METRIC_FLAG","1".equals((String)map.get("METRIC_FLAG"))?true:false);
            map.put("PATITION_KEY_FLAG","1".equals((String)map.get("PATITION_KEY_FLAG"))?true:false);
        }
        return list;
    }

    /**
     * 保存配置表右表数据
     * @param list
     */
    @Transactional
    public void saveTables(List<Map<String,Object>> list, HttpServletRequest request){
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        for (Map<String,Object> map : list){
            map.put("UPDATE_USER", userId);
            String str = (String) map.get("DATASOURCE_COLUMN_ID");
            if (str==null || "".equals(str)){
                map.put("DATASOURCE_COLUMN_ID",StringUtil.randomHexString(32));
                dataSourceMapper.insertTabledata(map);
            }else {
                dataSourceMapper.updataTable(map);
            }
        }
    }

    /**
     * 获取导出数据
     * @param paramMap
     * @return
     */
    public List<Map<String,Object>> getLeadingOutsData(Map<String,Object> paramMap){
        return dataSourceMapper.getLeadingOutsData(paramMap);
    }

    /**
     * 导出字段数据
     * @param paraMap
     * @return
     */
    public List<Map<String,Object>> getParamDatasourceColumn(Map<String,Object> paraMap){
        return dataSourceMapper.getParamDatasourceColumn(paraMap);
    }

    /**
     * 导入
     * @param data
     */
    @Transactional
    public String importExcel(List<List<Map<String,Object>>> data){
        boolean flag=true;
        String msg="";
        StringBuilder stringBuffer = new StringBuilder();
        StringBuilder stringBuffer1 = new StringBuilder();
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Calendar calendar = Calendar.getInstance();
        String date = df.format(calendar.getTime());
        Map<String,Object> databaseUserMap=new HashMap<>();
        List<Map<String, Object>> dataSourceType = (List<Map<String, Object>>)redisUtil.get("dataSourceTypeList");
        if (dataSourceType ==null){
            dataSourceType = dataSourceMapper.getDataSourceType(); //数据源类型
            redisUtil.set("dataSourceTypeList",dataSourceType,86400);
        }
        List<Map<String, Object>> physicaTableTypeList = null;//物理表类型
        List<Map<String, Object>> datasourceUse = (List<Map<String, Object>>)redisUtil.get("datasourceUseList");
        if (datasourceUse==null){
            datasourceUse = dataSourceMapper.getDatasourceUse();//数据源用途
            redisUtil.set("datasourceUseList",datasourceUse,86400);
        }
        List<Map<String, Object>> list =data.get(0);
        List<Map<String,Object>> list2 =data.get(1);
        List<Map<String, Object>> addList =new ArrayList<>();
        List<Map<String, Object>> updateList=new ArrayList<>();
        List<Map<String, Object>> addRowList =new ArrayList<>();
        List<Map<String, Object>> updateRowList=new ArrayList<>();
        for (int m = 0; m < list.size(); m++)  {
            boolean msgFlag=true;
            StringBuilder sbf=new StringBuilder();
            if (stringBuffer.toString().length()==0){
                sbf.append("<span style='color:blue;font-weight:bold;'>《数据源》:</span>");
            }
            sbf.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第<b>"+(m+2)+"</b>行数据:<br/>");
            Map<String,Object> map=list.get(m);
            String dataSourceDesc = (String) map.get("DATASOURCEDESC");
            String dataSourceUseDesc = (String) map.get("DATASOURCEUSEDESC");
            String physicatabletype = (String) map.get("PHYSICATABLETYPE");
            String databaseName = (String) map.get("DATABASENAME");
            String databaseUser = (String) map.get("DATABASEUSER");
            String databaseObjectName = (String) map.get("DATABASEOBJECTNAME");
            if ("启用".equals((String)map.get("ACTIVEDESC"))){
                map.put("ACTIVEDESC","1");
            }else {
                map.put("ACTIVEDESC","0");
            }
            for (int i = 0; i < dataSourceType.size(); i++) {
                Map<String,Object > dataSourceMap = dataSourceType.get(i);
                if (dataSourceDesc.equals((String) dataSourceMap.get("CNAME"))){
                    map.put("DATASOURCEDESC",(String) dataSourceMap.get("CODE"));
                    break;
                }else {
                    if (i==dataSourceType.size()-1){
                        msgFlag=false;
                        sbf.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<数据源类型></b>数据输入错误");
                    }
                }
            }
            for (int i = 0; i < datasourceUse.size(); i++) {
                Map<String,Object > useMap=datasourceUse.get(i);
                if (dataSourceUseDesc.equals((String) useMap.get("CNAME"))){
                    map.put("DATASOURCEUSEDESC",(String) useMap.get("CODE"));
                    break;
                }else {
                    if (i==datasourceUse.size()-1){
                        msgFlag=false;
                        sbf.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<数据源用途>数据输入错误");
                    }
                }
            }
            Pattern pattern = Pattern.compile("^[a-zA-Z]+[a-zA-Z0-9_]*(?<![_])$");
            if (!pattern.matcher(databaseObjectName).matches()) {
                msgFlag = false;
                sbf.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<数据库用户对象>数据输入非法");
            }else {
                List<Map<String, Object>> sourceData = dataSourceMapper.getDataSourceList(databaseName, databaseUser, databaseObjectName.toUpperCase(Locale.ENGLISH));
                map.put("date", date);
                if (sourceData.size() > 0) {
                    Map<String, Object> sourceMap = sourceData.get(0);
                    map.put("DATABASEUSER", (String) sourceMap.get("DATABASEUSERID"));
                    map.put("DATABASEOBJECTNAME", databaseObjectName.toUpperCase(Locale.ENGLISH));
                    map.put("DATASOURCEID", (String) sourceMap.get("DATASOURCEID"));
                    updateList.add(map);
                } else {
                    String databaseUserId = dataSourceMapper.getdatabaseUserData(databaseName, databaseUser);
                    if (!"".equals(databaseUserId) && databaseUserId != null) {
                        map.put("DATASOURCEID", StringUtil.randomHexString(32));
                        map.put("DATABASEOBJECTNAME", databaseObjectName.toUpperCase(Locale.ENGLISH));
                        map.put("DATABASEUSER", databaseUserId);
                        addList.add(map);
                    } else {
                        msgFlag = false;
                        sbf.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;系统中未创建该数据库用户或数据库名称");
                    }
                }

            }
            databaseUserMap.put("databaseUser",map.get("DATABASEUSER"));
            physicaTableTypeList = dataSourceMapper.getPhysicaTableTypeList(databaseUserMap);//物理表类型
            for (int i = 0; i < physicaTableTypeList.size(); i++) {
                Map<String, Object> phyysiTableTypeMap = physicaTableTypeList.get(i);
                if (physicatabletype.equals((String)phyysiTableTypeMap.get("CODE"))){
                    break;
                }else {
                    if (i==physicaTableTypeList.size()-1){
                        msgFlag=false;
                        sbf.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<物理表类型>数据输入错误<br/>");
                    }
                }
            }
            if (!msgFlag){
                if ("".equals(msg)){
                    msg="<span style='color:red;font-weight:bold;'>======>导入失败</span>(请您根据提示修正数据后再完成导入操作)<span style='color:red;font-weight:bold;'><=====</span> <br/>";
                }
               stringBuffer.append(sbf.toString());
                flag=false;
                continue;
            }
            //第二个工作薄的数据
            Map<String,Object> paramMap = new HashMap<String,Object>();
            String datasourceId=(String) map.get("DATASOURCEID");
            String databaseUserId =(String) map.get("DATABASEUSER");
            paramMap.put("databaseUserId",databaseUserId);
            paramMap.put("datasourceId",datasourceId);
            paramMap.put("flag",null);
            //获取左表未选择的字段信息
            List<Map<String, Object>> leftTableData = dataSourceMapper.getLeftTableData(paramMap);
            List<Map<String,Object>> datasourceColumnIdList = dataSourceMapper.getSourceColumnId(paramMap);
            for (int y = 0; y < list2.size(); y++)  {
                Map<String,Object> rowMap =list2.get(y);
                String rowDatabaseName = (String) rowMap.get("DATABASENAME");
                String rowDatabaseUser = (String) rowMap.get("DATABASE_USER");
                String rowDatabaseObjectName = (String) rowMap.get("DATABASE_OBJECT_NAME");
                if (databaseName.equals(rowDatabaseName)&& databaseUser.equals(rowDatabaseUser)&&databaseObjectName.toUpperCase(Locale.ENGLISH).equals(rowDatabaseObjectName.toUpperCase(Locale.ENGLISH))){
                    String DatabaseUserIds = (String) map.get("DATABASEUSER");
                    String databaseobjectname = (String) map.get("DATABASEOBJECTNAME");
                    String dataSourceId=(String)map.get("DATASOURCEID");
                    rowMap.put("DATABASE_USER",DatabaseUserIds);
                    rowMap.put("DATABASE_OBJECT_NAME",databaseobjectname.toUpperCase(Locale.ENGLISH));
                    rowMap.put("DATASOURCEID",datasourceId);
                    String rowColumnName = (String) rowMap.get("COLUMN_NAME");
                    for (int i = 0; i < leftTableData.size(); i++) {
                        Map<String, Object> leftMap = leftTableData.get(i);
                        String columnName = (String) leftMap.get("COLUMNNAME");
                        if (rowColumnName.equals(columnName)){
                            rowMap.put("date",date);
                            rowMap.put("userId",map.get("userId"));
                            rowMap.put("COLUMN_ID",(String) leftMap.get("COLUMNID"));
                            if (datasourceColumnIdList.size()==0){
                                rowMap.put("DATASOURCE_COLUMN_ID",StringUtil.randomHexString(32));
                                addRowList.add(rowMap);
                            }else {
                                for (int i1 = 0; i1 < datasourceColumnIdList.size(); i1++) {
                                    Map<String, Object> stringObjectMap=datasourceColumnIdList.get(i1);
                                    String columnid = (String)stringObjectMap.get("COLUMN_ID");
                                    if (columnid!=null&&leftMap.get("COLUMNID").equals(columnid)){
                                        rowMap.put("DATASOURCE_COLUMN_ID",(String)stringObjectMap.get("DATASOURCE_COLUMN_ID"));
                                        updateRowList.add(rowMap);
                                        break;
                                    }
                                }
                            }
                            break;
                        } else {
                            if (i==leftTableData.size()-1){
                                flag=false;
                                if ("".equals(msg)){
                                    msg="<span style='color:red;font-weight:bold;'>======>导入失败</span>(请您根据提示修正数据后再完成导入操作)<span style='color:red;font-weight:bold;'><=====</span> <br/>";
                                }
                                stringBuffer1.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第<b>"+(y+2)+"</b>行数据:<br/>");
                                stringBuffer1.append("<spanstyle='color:red;font-weight:bold;'>该表当中未配置该源字段名称的字段，请认真检查！</span> <br/>");
                            }
                        }
                    }
                }
            }
        }
        if (!flag){
            return msg+stringBuffer.toString()+(!"".equals(stringBuffer1.toString())?"<br/><span style='color:blue;font-weight:bold;'>《配置表》</span>":"")+stringBuffer1.toString();
        }
        //批量导入
        if (addList.size() > 0) {
                dataSourceMapper.addParamDatasource(addList);
        }
        //批量修改
        if (updateList.size() > 0){
                dataSourceMapper.updateParamDatasource(updateList);
        }
        if (addRowList.size()>0){
                dataSourceMapper.addParamDatasourceColumn(addRowList);
        }
        if (updateRowList.size()>0){
                dataSourceMapper.updateParamDatasourceColumn(updateRowList);
        }
        return "**********   <span style='color:green;font-weight:bold;'>导入结果：成功</span>   ********** <br/><br/>数据源共计导入 <span style='color:blue;font-weight:bold;'>"+ (addList.size()+updateList.size()) + "</span> 条数据<br/><br/>配置表共计导入 <span style='color:blue;font-weight:bold;'>" + (addRowList.size()+updateRowList.size()) + "</span> 条数据";
    }

    /**
     * 删除配置表数据
     * @param list
     */
    public void daleteTableData(List<String> list){
        dataSourceMapper.daleteTableData(list);
    }
}
